package persistence.dao.impl;

import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import persistence.dao.RoutsDao;
import persistence.entity.RoutsEntity;

public class RoutsDaoImpl implements RoutsDao  {

	private static final Logger logger = Logger.getLogger(RoutsDaoImpl.class);
	
	private static final String ROUTSDAOIMPL_INFO = "Could not getRouts"; 
	private static final String ROUTSDAOIMPL_INFO_INSERT = "Invalid Insert Routs"; 
	private static final String ROUTSDAOIMPL_INFO_DELETE = "Invalid Delete Routs"; 
	private static final String ROUTSDAOIMPL_INFO_GET = "Invalid Get Routs";
	private static final String ROUTSDAOIMPL_INFO_UPDATE = "Invalid UpDate Routs";
	
	private static final String GET_ROUTS_BY_ID_QUERY = "select id_routs, name_routs, date_routs from routs where id_routs = ?";

	private static final String GET_SEARCH_CITY_ONE_QUERY = "SELECT routs.id_routs, routs.name_routs, routs.date_routs FROM (city INNER JOIN route_stantion ON city.id_city = route_stantion.id_city) INNER JOIN routs ON route_stantion.id_routs = routs.id_routs WHERE (city.name_city like ? ) and ((routs.date_routs like ?) OR (routs.date_routs like ?))";

	private static final String GET_SEARCH_CITY_TWO_QUERY = "SELECT routs.id_routs, routs.name_routs, routs.date_routs FROM (city INNER JOIN route_stantion ON city.id_city = route_stantion.id_city) INNER JOIN routs ON route_stantion.id_routs = routs.id_routs WHERE (city.name_city like ? ) and ((routs.date_routs like ?) OR (routs.date_routs like ?))";

	private static final String INSERT_ROUTS = "INSERT INTO `routs`(name_routs, date_routs) VALUES (?,?)";
		
	private static final String GET_ALL_ROUTS = "select id_routs, name_routs, date_routs from `routs`";
		
	private static final String GET_ROUTS_BY_ID = "select id_routs, name_routs, date_routs from routs where id_routs=? ";
	
	private static final String SET_ROUTS_UPDATE = "UPDATE `routs` SET name_routs = ?, date_routs = ? where id_routs=? ";
	
	private static final String DELETE_ROUTS ="DELETE from `routs` WHERE id_routs=?";
	
	private static final String GET_ALL_ROUTS_STANTION = "SELECT city.id_city, city.name_city, route_stantion.id, route_stantion.id_routs, route_stantion.id_city, routs.id_routs, routs.name_routs, routs.date_routs FROM (city INNER JOIN route_stantion ON city.id_city = route_stantion.id_city) INNER JOIN routs ON route_stantion.id_routs = routs.id_routs";

	
	private Connection connection;
	public RoutsDaoImpl(Connection conn){
		this.connection = conn;
	}
	
	@Override
	public Class<RoutsEntity> getEntityClass() {
		return null;
	}

	@Override
	public List<RoutsEntity> findAll() {
		return null;
	}

	@Override
	public List<RoutsEntity> findByExample(RoutsEntity exampleInstance) {
		return null;
	}

	@Override
	public int countAll() {
		return 0;
	}

	@Override
	public int countByExample(RoutsEntity exampleInstance) {
		return 0;
	}

	@Override
	public RoutsEntity insert(RoutsEntity entity) {
		return null;
	}

	@Override
	public RoutsEntity update(RoutsEntity entity) {
		return null;
	}
	
	@Override
	public List<RoutsEntity> getRouts() {	
		List<RoutsEntity> routs = new ArrayList<RoutsEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			stmt = connection.prepareStatement(RoutsDaoImpl.GET_ALL_ROUTS);
			rs = stmt.executeQuery();
			while (rs.next()) {
				RoutsEntity rout = new RoutsEntity();
				rout.setId_routs(rs.getInt("id_routs"));
				rout.setName_routs(rs.getString("name_routs"));
				rout.setDate_routs(rs.getString("date_routs"));
				routs.add(rout);
			}
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return routs;
	}
	
	@Override
	public List<RoutsEntity> getRoutsId(String searchFrom, String searchTo,
			String date_routs) {
		
		List<RoutsEntity> routs = new ArrayList<RoutsEntity>();
		PreparedStatement stmt_one = null;
		ResultSet rs_one = null;

		PreparedStatement stmt_two = null;
		ResultSet rs_two = null;

		try {
			String metka = null;
			if ((Integer.parseInt(date_routs.substring(0, 2)) % 2) == 0)
				metka = "even";
			else if ((Integer.parseInt(date_routs.substring(0, 2)) % 1) == 0)
				metka = "odd";
			if (searchFrom != null && searchTo != null) {
				stmt_one = connection.prepareStatement(RoutsDaoImpl.GET_SEARCH_CITY_ONE_QUERY);
				stmt_one.setString(1, "%" + searchFrom + "%");
				stmt_one.setString(2, "%" + metka + "%");
				stmt_one.setString(3, "%" + "every" + "%");
				
				stmt_two = connection.prepareStatement(RoutsDaoImpl.GET_SEARCH_CITY_TWO_QUERY);
				stmt_two.setString(1, "%" + searchTo + "%");
				stmt_two.setString(2, "%" + metka + "%");
				stmt_two.setString(3, "%" + "every" + "%");
			} else {
			}
			rs_one = stmt_one.executeQuery();
			rs_two = stmt_two.executeQuery();

			while (rs_one.next()) {
				RoutsEntity rout = new RoutsEntity();
				while (rs_two.next()) {
					if (rs_one.getInt("id_routs") == rs_two.getInt("id_routs")) {		
						rout.setId_routs(rs_one.getInt("id_routs"));
						rout.setName_routs(rs_one.getString("name_routs"));
						rout.setDate_routs(rs_one.getString("date_routs"));
						routs.add(rout);
							break;
					}
				}
				rs_two = stmt_two.executeQuery();
			}
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs_one != null || rs_two != null) {
				try {
					rs_one.close();
					rs_two.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt_one != null || stmt_two != null) {
				try {
					stmt_one.close();
					stmt_two.close();
				} catch (Exception ignore) {
				}
			}
		}
		return routs;
	}

	@Override
	public RoutsEntity routsById(int id) {
		
		RoutsEntity routs = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.prepareStatement(RoutsDaoImpl.GET_ROUTS_BY_ID_QUERY);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next())
            {
            	routs = new RoutsEntity();
            	routs.setId_routs(rs.getInt("id_routs"));
            	routs.setName_routs(rs.getString("name_routs"));
            	routs.setDate_routs(rs.getString("date_routs"));
            }
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return routs;
	}
	
	@Override
	public RoutsEntity setRouts(String add_routs, String add_date) {

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			if (add_routs !=null && add_date != null) {
				stmt = connection.prepareStatement(RoutsDaoImpl.INSERT_ROUTS);
				stmt.setString(1, add_routs);
				stmt.setString(2, add_date);
			} else {
				logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO_INSERT );
			}
			stmt.executeUpdate();
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {

			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return null;
	}

	@Override
	public RoutsEntity delete(int[] id_update) {
		
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			if (id_update != null)
			{	
				for(int i=0; i<id_update.length;i++){
					stmt = connection.prepareStatement(RoutsDaoImpl.DELETE_ROUTS);
					stmt.setInt(1, id_update[i]);
					stmt.executeUpdate();
				}
			}else{
				logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO_DELETE );
			}
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return null;
	}
	
	@Override
	public List<RoutsEntity> getRoutsById(int[] id_update) {
		
		List<RoutsEntity> routsUp = new ArrayList<RoutsEntity>();
		
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			if (id_update != null)
			{	
				for(int i=0; i<id_update.length;i++){
					stmt = connection.prepareStatement(RoutsDaoImpl.GET_ROUTS_BY_ID);
					stmt.setInt(1, id_update[i]);
					rs = stmt.executeQuery();
					rs.next();
					RoutsEntity routs = new RoutsEntity();
					routs.setId_routs(rs.getInt("id_routs"));
					routs.setName_routs(rs.getString("name_routs"));
					routs.setDate_routs(rs.getString("date_routs"));
					routsUp.add(routs);
				}
			}else{
				logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO_GET );
			}
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return routsUp;
	}

	@Override
	public RoutsEntity setRoutsUpdate(int[] id_update, String[] name_routs,
			String[] date_routs) {
		
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			if (id_update != null)
			{	
				for(int i=0; i<id_update.length;i++){
					stmt = connection.prepareStatement(RoutsDaoImpl.SET_ROUTS_UPDATE);
					stmt.setString(1, name_routs[i]);
					stmt.setString(2, date_routs[i]);
					stmt.setInt(3, id_update[i]);
					stmt.executeUpdate();
				}
			}else{
				logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO_UPDATE);
			}
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return null;
	}

	@Override
	public List<RoutsEntity> getRoutsStantion() {
		List<RoutsEntity> routs = new ArrayList<RoutsEntity>();
	
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			stmt = connection.prepareStatement(RoutsDaoImpl.GET_ALL_ROUTS_STANTION);
			rs = stmt.executeQuery();
			while (rs.next()) {
				RoutsEntity rout = new RoutsEntity();
				rout.setId_city(rs.getInt("city.id_city"));			
				rout.setName_city(rs.getString("city.name_city"));
				rout.setId(rs.getInt("route_stantion.id"));
				rout.setId_stantion_routs(rs.getInt("route_stantion.id_routs"));
				rout.setId_stantion_city(rs.getInt("route_stantion.id_city"));
				rout.setId_routs(rs.getInt("routs.id_routs"));
				rout.setName_routs(rs.getString("routs.name_routs"));
				rout.setDate_routs(rs.getString("routs.date_routs"));
				routs.add(rout);
			}
		} catch (Exception e) {
			logger.error(RoutsDaoImpl.ROUTSDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return routs;
	}
}
